package com.example.batchdemo.config;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelHandler extends DefaultHandler {
	private SharedStringsTable sst;
	private String lastContents;
	private boolean nextIsString;

//	private int curRowIdx = 1;
	private String curCellName = "";
	private String curSheetName = "";

	private static final String COLUMN_NAME_ROW_NUM = "rowNum";
	private static final String COLUMN_NAME_REG = "^([A-Z]{1,})";

	private LinkedHashMap<String, List<LinkedHashMap<String, String>>> sheetList = new LinkedHashMap<String, List<LinkedHashMap<String, String>>>();

	private List<LinkedHashMap<String, String>> dataList = new ArrayList<LinkedHashMap<String, String>>();

	private LinkedHashMap<String, String> curRowMap = new LinkedHashMap<String, String>();

	public LinkedHashMap<String, List<LinkedHashMap<String, String>>> getAllSheetDataList() {
		return sheetList;
	}

	public void fetchRow(Map<String, String> rowMap) {
		LinkedHashMap<String, String> dataMap = new LinkedHashMap<String, String>();
		rowMap.forEach((k, v) -> dataMap.put(getColumnName(k), v));
		dataList.add(dataMap);
	};

	public void readOneSheet(String filePath, String sheetName) throws Exception {
		OPCPackage pkg = null;

		try {
			pkg = OPCPackage.open(filePath);
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sst = r.getSharedStringsTable();

			XMLReader parser = fetchSheetParser(sst);

			boolean isSheetExist = false;
			SheetIterator iter = (SheetIterator) r.getSheetsData();
			while (iter.hasNext()) {
				try (InputStream sheet = iter.next()) {
					curSheetName = iter.getSheetName();
					if (sheetName.equalsIgnoreCase(curSheetName)) {
						isSheetExist = true;
						InputSource sheetSource = new InputSource(sheet);
						parser.parse(sheetSource);
					}
					sheet.close();
				}
			}

			if (!isSheetExist) {
				throw new Exception("Sheet(" + sheetName + ") doesn't exist. ");
			}
		} finally {
			if (null != pkg) {
				pkg.close();
			}
		}
	}

	public void readAllSheets(String filePath) throws Exception {
		OPCPackage pkg = null;

		try {
			pkg = OPCPackage.open(filePath);
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sst = r.getSharedStringsTable();

			XMLReader parser = fetchSheetParser(sst);

			SheetIterator iter = (SheetIterator) r.getSheetsData();
			while (iter.hasNext()) {
				try (InputStream sheet = iter.next()) {
					curSheetName = iter.getSheetName();
					InputSource sheetSource = new InputSource(sheet);
					parser.parse(sheetSource);
					sheet.close();
				}
			}
		} finally {
			if (null != pkg) {
				pkg.close();
			}
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader("com.sun.org.apache.xerces.internal.parsers.SAXParser");
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}

	@Override
	public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
		if (name.equals("worksheet")) {
			dataList = new ArrayList<LinkedHashMap<String, String>>();
		}

		if (name.equals("row")) {
			String rowNum = attributes.getValue("r");
			curRowMap.put(COLUMN_NAME_ROW_NUM, rowNum);
		}

		// c => Cell
		if (name.equals("c")) {
			String cellType = attributes.getValue("t");
			if (cellType != null && cellType.equals("s")) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
		}

		lastContents = "";

		String cellName = attributes.getValue("r");
		if (cellName != null && !cellName.isEmpty()) {
			curCellName = cellName;
		}
	}

	@Override
	public void endElement(String uri, String localName, String name) throws SAXException {
		if (nextIsString) {
			try {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
			} catch (Exception e) {

			}
		}

		if (name.equals("v")) {
			String value = lastContents.trim();
			value = value.equals("") ? " " : value;
			curRowMap.put(curCellName, value);
		} else {

			if (name.equals("row")) {
				fetchRow(curRowMap);
				curRowMap.clear();
//				curRowIdx++;
			}
		}

		if (name.equals("worksheet") && curSheetName != null && curSheetName.length() > 0) {
			sheetList.put(curSheetName, dataList);
		}
	}

	public void characters(char[] ch, int start, int length) throws SAXException {
		lastContents += new String(ch, start, length);
	}

	public static String dateNum2ISODateStr(String dateNum) {
		String result = "";

		if (StringUtils.isBlank(dateNum)) {
			result = "";
		} else {
			try {
				if (StringUtils.isNumeric(dateNum)) {
					Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(dateNum));
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

					result = formatter.format(date);
				} else {
					result = dateNum;
				}
			} catch (Exception ex) {
				result = "";
			}
		}

		return result;
	}

	private String getColumnName(String cellName) {
		if (COLUMN_NAME_ROW_NUM.equals(cellName)) {
			return COLUMN_NAME_ROW_NUM;
		} else {
			Pattern pattern = Pattern.compile(COLUMN_NAME_REG);
			Matcher m = pattern.matcher(cellName);
			if (m.find()) {
				return m.group(1);
			}
		}

		return "";
	}

}
